﻿using System;
using System.Collections.Generic;
using System.Text;

namespace MyDLL
{
    /// <summary>
    /// 数据库相关
    /// </summary>
    public class DBUtility
    {
        public class OleDBUtility
        {
            /// <summary>
            /// GetConnectionString，适用于Excel文件。
            /// </summary>
            /// <param name="excel"></param>
            /// <param name="filePath">Excel文件的完整路径</param>
            /// <param name="HDR">是否有Headers，默认为是。</param>
            /// <param name="IMEX">是否将所有列视为文本，默认为否。</param>
            /// <returns></returns>
            public static string GetConnectionString(DBType.Excel excel, string filePath, bool HDR = true, bool IMEX = false)
            {
                string connStr, hdr, imex;
                if (HDR)
                    hdr = "YES";
                else
                    hdr = "NO";
                if (IMEX)
                    imex = ";IMEX=1";
                else
                    imex = "";
                switch (excel)
                {
                    case DBType.Excel.Xlsxm:
                        connStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0; HDR = {1}{2}\"", filePath, hdr, imex);
                        return connStr;
                    case DBType.Excel.Xlsx:
                        connStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml; HDR = {1}{2}\"", filePath, hdr, imex);
                        return connStr;
                    case DBType.Excel.Xlsm:
                        connStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Macro; HDR = {1}{2}\"", filePath, hdr, imex);
                        return connStr;
                    case DBType.Excel.Xls:
                        connStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0; HDR = {1}{2}\"", filePath, hdr, imex);
                        return connStr;
                    default:
                        return "";
                }
            }

            /// <summary>
            /// GetConnectionString，适用于DBF文件。
            /// </summary>
            /// <param name="dbf"></param>
            /// <param name="path">dbf文件的存放路径，不包括文件名。</param>
            /// <param name="userID">用户名，默认为Admin</param>
            /// <param name="password">密码，默认为空</param>
            /// <returns></returns>
            public static string GetConnectionString(DBType.DBF dbf, string path, string userID = "Admin", string password = "")
            {
                string connStr = string.Empty;
                switch (dbf)
                {
                    case DBType.DBF.DBF:
                        connStr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=dBASE IV;User ID={1};Password={2};", path, userID, password);
                        return connStr;
                    default:
                        return connStr;
                }
            }

            /// <summary>
            /// GetConnectionString，适用于Text File。
            /// </summary>
            /// <param name="textFile"></param>
            /// <param name="path">Text File文件的存放路径，不包括文件名。</param>
            /// <param name="HDR">是否有Headers，默认为是。</param>
            /// <param name="FMT">分隔方式，默认为用“;”分隔，如果用Tab分隔，则输入“Tab”</param>
            /// <returns></returns>
            public static string GetConnectionString(DBType.TextFile textFile, string path, bool HDR = true, string FMT = ";")
            {
                string connStr = string.Empty, hdr = string.Empty, fmt = string.Empty;
                if (HDR)
                    hdr = "YES";
                else
                    hdr = "NO";
                if (FMT == ";")
                {
                    fmt = "Delimited(;)";
                }
                else if (FMT == "Tab")
                {
                    fmt = "TabDelimited";
                }
                switch (textFile)
                {
                    case DBType.TextFile.CSV:
                        connStr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"text; HDR = {1}; FMT = {2}\";", path, hdr, fmt);
                        return connStr;
                    case DBType.TextFile.FixedLengthColumns:
                        connStr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"text; HDR = {1}; FMT = Fixed\";", path, hdr);
                        return connStr;
                    default:
                        return connStr;
                }
            }

            /// <summary>
            /// GetConnectionString，适用于Access文件。
            /// </summary>
            /// <param name="access"></param>
            /// <param name="path">Access文件的完整路径，包括文件名。</param>
            /// <returns></returns>
            public static string GetConnectionString(DBType.Access access, string path)
            {
                string connStr = string.Empty;
                switch (access)
                {
                    case DBType.Access.Access_2003:
                        connStr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};User Id=admin;Password=;", path);
                        break;
                    case DBType.Access.Access_2007:
                        connStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Jet OLEDB:Database Password=MyDbPassword;", path);
                        break;
                    default:
                        break;
                }
                return connStr;
            }
        }
    }

    /// <summary>
    /// 
    /// </summary>
    public class DBType
    {
        /// <summary>
        /// 适用于Excel文件
        /// </summary>
        public enum Excel
        {
            /// <summary>
            /// Xlsx和Xlsm通用，有标题行。
            /// Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myBinaryExcel2007file.xlsb;Extended Properties="Excel 12.0;HDR=YES";
            /// https://www.connectionstrings.com/excel-2007/
            /// </summary>
            Xlsxm,

            /// <summary>
            /// 有标题行
            /// Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";
            /// https://www.connectionstrings.com/excel-2007/
            /// </summary>
            Xlsx,

            /// <summary>
            /// Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsm;Extended Properties="Excel 12.0 Macro;HDR=YES";
            /// https://www.connectionstrings.com/excel-2007/
            /// </summary>
            Xlsm,

            /// <summary>
            /// Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;";
            /// https://www.connectionstrings.com/excel-2003/
            /// </summary>
            Xls
        }

        /// <summary>
        /// 适用于DBF文件
        /// </summary>
        public enum DBF
        {
            /// <summary>
            /// Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\folder;Extended Properties=dBASE IV;User ID=Admin;Password=;
            /// https://www.connectionstrings.com/dbf-foxpro/
            /// </summary>
            DBF
        }

        /// <summary>
        /// 适用于文本文件，如csv、txt等。
        /// </summary>
        public enum TextFile
        {
            /// <summary>
            /// Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;Extended Properties="text;HDR=Yes;FMT=Delimited";
            /// 
            /// The delimiter can be specified in the registry at the following location:
            /// HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Jet \ 4.0 \ Engines \ Text
            /// "Format" = "TabDelimited"
            /// or
            /// "Format" = "Delimited(;)"
            /// https://www.connectionstrings.com/textfile/
            /// </summary>
            CSV,

            /// <summary>
            /// Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;Extended Properties="text;HDR=Yes;FMT=Fixed";
            /// https://www.connectionstrings.com/textfile/
            /// </summary>
            FixedLengthColumns
        }

        /// <summary>
        /// 适用于Access数据库文件
        /// </summary>
        public enum Access
        {
            /// <summary>
            /// Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;Password=;
            /// https://www.connectionstrings.com/access-2003/
            /// </summary>
            Access_2003,

            /// <summary>
            /// Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;Jet OLEDB:Database Password=MyDbPassword;
            /// https://www.connectionstrings.com/access-2007/
            /// </summary>
            Access_2007
        }
    }
}
